ALTER    PROC SP_CREATE_UPDATE_TEST_LOT_BIN(
@PART_NUMBER VARCHAR(50), 
@LOT_NUMBER VARCHAR(50), 
@BIN_NUMBER VARCHAR(10),
@BIN_QTY INT,
@VENDOR_NAME VARCHAR(50),
@PS_TYPE VARCHAR(50),
@BIN_TYPE VARCHAR(50),
@DATE_IN  VARCHAR(50),
@QTY_IN   INT,
@QTY_OUT  INT
)

AS
BEGIN

	DECLARE @PROCESS_STEP_ID INT
	DECLARE @LOCATION_ID INT
	DECLARE @LOT_ID INT
	DECLARE @MAX_TIMESTAMP DATETIME
 	DECLARE @LOT_CURRENT_STATUS VARCHAR(20)
 	DECLARE @TEMP_QTY_OUT INT
 	DECLARE @TEMP_QTY_IN  INT
 	DECLARE @TEMP_DATE_IN DATETIME
 	DECLARE @TEMP_DATE_OUT DATETIME
	DECLARE @ASSM_RELEASE_NO VARCHAR(50)

	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'TEST' AND L_VENDOR_NAME = @VENDOR_NAME
	IF @PS_TYPE IS NULL
	BEGIN
		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER= @PART_NUMBER  AND PS_TYPE_NAME = 'TEST' AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
	
		IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
		BEGIN
			SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME='TEST' AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PRODUCT_MAP_TABLE.LOCATION_ID = @LOCATION_ID
			IF @PROCESS_STEP_ID IS NULL
			BEGIN
				-- DUMMY UPDATE TO AVOID SQLEXCEPTION
				UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
				--RETURN
			END
		END
	END
	ELSE
	BEGIN
		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER= @PART_NUMBER  AND PS_TYPE_NAME = 'TEST' AND PS_TYPE=@PS_TYPE AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
	
		IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
		BEGIN
			SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME='TEST' AND PS_TYPE=@PS_TYPE AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PRODUCT_MAP_TABLE.LOCATION_ID = @LOCATION_ID 
			IF @PROCESS_STEP_ID IS NULL
			BEGIN
				-- DUMMY UPDATE TO AVOID SQLEXCEPTION
				UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
				--RETURN
			END
		END
	END

	
	SELECT  @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
	
	
	IF @LOT_ID IS NULL AND @PROCESS_STEP_ID IS NOT NULL
	BEGIN
		INSERT INTO LOT_TABLE ( PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, LOT_CURRENT_STATUS,LOT_ENTERED_BY, TIMESTAMP) VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, 'CLOSE' ,1, CURRENT_TIMESTAMP)
  		SET @LOT_ID = @@IDENTITY
 		INSERT INTO FINAL_TEST_LOT_HISTORY (LOT_ID, FTL_DEVICE_TYPE, FTL_DATE_IN, FTL_QTY_IN, FTL_QTY_OUT, FTL_YIELD,FTL_BINNING_INFO, TIMESTAMP,FTL_CURRENT_STATUS ) VALUES (@LOT_ID, @PART_NUMBER,@DATE_IN, @QTY_IN, @QTY_OUT, (@QTY_OUT * 100)/@QTY_IN, 0, CURRENT_TIMESTAMP, 'CLOSE')
	END
	ELSE
	BEGIN
		SELECT @MAX_TIMESTAMP = MAX(FINAL_TEST_LOT_HISTORY.Timestamp) FROM FINAL_TEST_LOT_HISTORY WHERE Lot_ID = @LOT_ID AND FTL_CURRENT_STATUS = 'CLOSE'
 		
 		IF @MAX_TIMESTAMP IS NOT NULL
 		BEGIN
 			SELECT @LOT_CURRENT_STATUS = FTL_CURRENT_STATUS FROM FINAL_TEST_LOT_HISTORY WHERE LOT_ID = @LOT_ID AND FINAL_TEST_LOT_HISTORY.TIMESTAMP = @MAX_TIMESTAMP 
 			IF @LOT_CURRENT_STATUS = 'CLOSE'
 			BEGIN
 				
 				UPDATE FINAL_TEST_LOT_HISTORY SET  FTL_DATE_IN=@DATE_IN, FTL_QTY_IN=@QTY_IN, FTL_QTY_OUT=@QTY_OUT, FTL_YIELD = (@QTY_OUT * 100)/@QTY_IN WHERE LOT_ID = @LOT_ID AND FINAL_TEST_LOT_HISTORY.TIMESTAMP = @MAX_TIMESTAMP AND FTL_CURRENT_STATUS = 'CLOSE'
 			END
 			ELSE 
 			BEGIN
 				UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT NUMBER' WHERE LOT_NUMBER = 'LOT NUMBER'
 			END
 		END
 		ELSE
 		BEGIN
 			INSERT INTO FINAL_TEST_LOT_HISTORY (LOT_ID, FTL_DEVICE_TYPE, FTL_DATE_IN, FTL_QTY_IN, FTL_QTY_OUT, FTL_YIELD,FTL_BINNING_INFO, TIMESTAMP,FTL_CURRENT_STATUS ) VALUES (@LOT_ID, @PART_NUMBER,@DATE_IN, @QTY_IN, @QTY_OUT, (@QTY_OUT * 100)/@QTY_IN, 0, CURRENT_TIMESTAMP, 'CLOSE')			
 		END
 		
 		SET @MAX_TIMESTAMP = NULL

		IF ( SELECT COUNT(*) FROM TEST_BIN_LOT_TABLE WHERE LOT_ID = @LOT_ID AND BIN_NUMBER = @BIN_NUMBER)  = 0
		BEGIN
			INSERT INTO TEST_BIN_LOT_TABLE (LOT_ID, BIN_NUMBER, BIN_TYPE, QUANTITY, TIMESTAMP) VALUES (@LOT_ID, @BIN_NUMBER, @BIN_TYPE, @BIN_QTY, CURRENT_TIMESTAMP)
		END
		ELSE
		BEGIN
			
			UPDATE TEST_BIN_LOT_TABLE SET LOT_ID = @LOT_ID, BIN_NUMBER = @BIN_NUMBER, BIN_TYPE = @BIN_TYPE, QUANTITY = QUANTITY + @BIN_QTY, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID AND BIN_NUMBER = @BIN_NUMBER
		END
	END
	
	/* For Closing the Assembly Release Lots */
	DECLARE @assemblyreleaseCursor CURSOR
	SET @assemblyreleaseCursor = CURSOR FOR SELECT DISTINCT AR.ASSEMBLY_RELEASE_NO FROM ASSM_RELEASE_LOT_TABLE ARL,ASSM_RELEASE_TABLE AR WHERE ARL.ASSEMBLY_RELEASE_ID = AR.ASSEMBLY_RELEASE_ID and STATUS = 'APPROVED'
	OPEN @assemblyreleaseCursor
	FETCH NEXT FROM @assemblyreleaseCursor INTO @ASSM_RELEASE_NO
				
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @ASSM_RELEASE_NO = @LOT_NUMBER
		BEGIN
			UPDATE ASSM_RELEASE_TABLE SET STATUS = 'Closed' WHERE ASSEMBLY_RELEASE_NO = @ASSM_RELEASE_NO
		END
			
	FETCH NEXT FROM @assemblyreleaseCursor INTO @ASSM_RELEASE_NO
	END
	CLOSE @assemblyreleaseCursor
	DEALLOCATE @assemblyreleaseCursor
	
	/* END OF CLOSING ASSEMBLY RELESE LOTS */

END